﻿/*

 gd20090719
 
 The connection manager handles the various connections
 to the Oracle databases.
  
 We mantain an array of connections to the database (connList). Each connection 
 is known to the caller by its id.
 
 We mantain also an array of queries.
 
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

namespace projectOklahoma
{
    public struct tquery
    {
        public System.Collections.ArrayList recordCache;
        public bool fetchComplete;
        public OracleDataReader currentReader;
    }

    static class OracleConnectionManager
    {
        static int initialFetchCount = 25;
        static System.Collections.ArrayList connList;
        static System.Collections.ArrayList queryList;

        static OracleConnectionManager()
        {
            connList = new System.Collections.ArrayList();
            queryList = new System.Collections.ArrayList();
        }

        public static bool isFetchComplete(int queryId)
        {
            return ((tquery)queryList[queryId]).fetchComplete;
        }

        public static int newConnection(string username, string password, string db, ref string errorMsg)
        {
            try
            {
                // see if ORACLE_HOME is set
                string oraHome = System.Environment.GetEnvironmentVariable("ORACLE_HOME");
                if (oraHome == "")
                {
                    errorMsg="ORACLE_HOME not set. Please, set your ORACLE_HOME environment variable.";
                    return -1;
                }

                string connectionString = "Data Source=" + db + ";User ID=" + username + "; Password=" + password+";";
                OracleConnection con = new OracleConnection(connectionString);
                con.Open();

                connList.Add(con);
                return connList.Count - 1;
            }
            catch (Exception e)
            {
                errorMsg="Error on connection: "+e.Message.ToString();
                return -1;
            }
        }

        public static int executeQuery(string q, int cnn, ref string errorstring,DataGridView dgv,ref int queryId)
        {
            try
            {
                OracleCommand command = new OracleCommand(q, (System.Data.OracleClient.OracleConnection)connList[cnn]);
                OracleDataReader reader;
                reader = command.ExecuteReader();

                tquery newQuery = new tquery();
                newQuery.currentReader = reader;
                newQuery.fetchComplete = false;
                newQuery.recordCache = new System.Collections.ArrayList();

                // prepare datagrid's columns

                dgv.Columns.Clear();
                for (int c = 0; c < reader.FieldCount; c++)
                {
                    dgv.Columns.Add(reader.GetName(c), reader.GetName(c));
                }

                // try to read some records and store them in an array

                //recordCache.Clear();

                int rec = 0;
                while (rec < initialFetchCount)
                {
                    if (reader.Read())
                    {
                        object[] values = new object[reader.FieldCount];
                        reader.GetValues(values);
                        newQuery.recordCache.Add(values);
                        rec++;
                    }
                    else
                    {
                        newQuery.fetchComplete = true;
                        dgv.RowCount = rec;
                        break;
                    }
                }

                if (!newQuery.fetchComplete)
                {
                    dgv.RowCount = initialFetchCount;
                }

                queryList.Add(newQuery);
                queryId = queryList.Count - 1;
            }
            catch (Exception e)
            {
                errorstring = e.Message.ToString();
                queryId = -1;
                return 1;
            }

            return 0;
        }

        public static void getQueryData(int row, int col, ref object o, DataGridView dgv,int queryId)
        {
            tquery curq = (tquery)queryList[queryId];

            if (row < curq.recordCache.Count)
            {
                o = ((object[])curq.recordCache[row])[col];
            }
            else if (curq.fetchComplete)
            {
                return;
            }
            else
            {
                // we need to fetch some more data
                int rec = 0;
                while (rec < ((row + 1) - curq.recordCache.Count))
                {
                    if (curq.currentReader.Read())
                    {
                        object[] values = new object[curq.currentReader.FieldCount];
                        curq.currentReader.GetValues(values);
                        curq.recordCache.Add(values);
                        rec++;
                    }
                    else
                    {
                        curq.fetchComplete = true;
                        break;
                    }
                }

                o = ((object[])curq.recordCache[row])[col];
            }
        }

        public static void closeConnections()
        {
            foreach (var conn in connList)
            {
                ((System.Data.OracleClient.OracleConnection)conn).Close();
            }
        }
    }
}
